package com.nj.dms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.nj.dms.bean.User;
import com.nj.dms.dao.UserDao;
import com.nj.dms.util.DbUtil;

/**
 * UserDao接口的实现类 用mysql数据库完成用户类数据的增加、删除、修改、查询等操作
 * 
 * @author 203
 *
 */
public class UserDaoImpl implements UserDao {


	@Override
	public boolean addUser(User user) {
		// 1 获取数据库的Connection对象

		Connection conn = DbUtil.dbOpen();
		// 2定义一个PreparedStatement类的对象pstmt
		PreparedStatement pstmt = null;
		// 准备增加的sql语句，并作为参数传入preparedStatent对象

		// `name`
		// `user_name`
		// `password`
		// `type`
		// `birthday`
		// `sex`
		// `branch`
		// `add_date`
		// `update_date`
		String sql = "insert into user(name,user_name,password,type,birthday,sex,branch,add_date,update_date) values(?,?,?,?,?,?,?,?,?);";
		// 定义一个保存增加成功返回的插入记录个数
		int result = 0;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		try {
			// 调用Connection类的prepareStatement(参数)，返回PreparedStatement类的对象pstmt
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getUserName());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getType());
			pstmt.setString(5, sdf.format(user.getBirthday()));
			pstmt.setInt(6, user.getSex());
			pstmt.setInt(7, user.getBranch());
			pstmt.setString(8, sdf.format(user.getAddDate()));
			pstmt.setString(9, sdf.format(user.getUpdateDate()));

			// 调用PreparedStatement类的executeUpdate()执行插入操作，并返回插入的结果
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 调用DbUtil类的静态方法dbClose()释放数据库资源
			DbUtil.dbClose(conn, pstmt);
		}
		// 如果插入成功 返回true 否则返回false
		return result > 0;
	}

	// 根据 ID号删除一条记录
	@Override
	public boolean deleteUser(int id) {
		Connection con = DbUtil.dbOpen();
		PreparedStatement pstmt = null;
		String sql = "delete from user where id=?";
		int result = 0;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(con, pstmt);
		}
		return result > 0;
	}

	// 根据参数User对象修改数据库user表中的id对应的一条用户信息
	@Override
	public boolean updateUser(User user) {
		Connection con = DbUtil.dbOpen();
		PreparedStatement pst = null;
		int i = 0;
		try {
			pst = con.prepareStatement("update user set name=?,password=?,age=?,sex=? where id=?");
			pst.setString(1, user.getName());
			pst.setString(2, user.getPassword());

			pst.setInt(4, user.getSex());
			pst.setInt(5, user.getId());
			i = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(con, pst);
		}
		return i > 0;
	}

	// 根据ID号从数据库user表查询id对应的一条记录到User对象
	@Override
	public User findUser(int id) {
		// 实例化一个User类的对象u用于保存查询的结果
		User user = new User();
		Connection con = DbUtil.dbOpen();
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			pst = con.prepareStatement("select * from user where id=?");
			pst.setInt(1, id);
			// 调用preparedStatment类的executeQuery()方法，返回查询的结果记录集合
			rs = pst.executeQuery();
			if (rs != null && rs.next()) {
				// 如果查询到当前id记录,将记录数据赋值给u对象
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setType(rs.getInt("type"));
				user.setBirthday(new Date(rs.getTimestamp("birthday").getTime()));
				user.setSex(rs.getInt("sex"));
				user.setBranch(rs.getInt("branch"));
				user.setAddDate(new Date(rs.getTimestamp("add_date").getTime()));
				user.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));
			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst, rs);
		}

		return user;
	}

	/**
	 * 返回所有的用户信息到List集合
	 */
	@Override
	public List<User> findUsers() {
		// 实例化一个List集合存放查询到时的所有的用户信息
		List<User> listUsers = new ArrayList();

		// 1 获取数据库的Connection对象
		Connection conn = DbUtil.dbOpen();
		// 2定义一个PreparedStatement类的对象pstmt
		PreparedStatement pstmt = null;
		// 3定义一个ResultSet类的对象rs返回查询的结果集
		ResultSet rs = null;
		// 准备增加的sql语句，并作为参数参数传入preparedStatent对象
		String sqlstr = "select * from user;";
		try {
			pstmt = conn.prepareStatement(sqlstr);
			// 调用PreparedStatement类的executeQuery，返回查询结果记录集合
			rs = pstmt.executeQuery();
			// 通过循环获取每一条记录的内容，并插入到List集合
			while (rs.next()) {
				// 读取当前一条记录内容
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setUserName(rs.getString("username"));
				user.setPassword(rs.getString("password"));

				// 保存在List集合
				listUsers.add(user);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}
		// 返回查询的用户信息
		return listUsers;
	}

	public static void main(String[] args) {

		UserDaoImpl daoImpl = new UserDaoImpl();

		User user = new User();

		user.setUserName("user1");
		user.setPassword("123456");

		System.out.println(daoImpl.findUserByLogin(user));

	}

	/**
	 * 判断输入的用户名和密码在数据库是否存在，存在则返回true 不存在则返回false
	 * 
	 * @throws SQLException
	 */
	@Override
	public boolean findUserByLogin(User user) {

		if (user == null || user.getUserName() == null || user.getPassword() == null) {

			return false;

		}

		Connection conn = DbUtil.dbOpen();
		boolean flag = false;

		PreparedStatement pstmt = null;

		try {
			String sql = "select name from user where user_name=? and password=? and type=?";

			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setInt(3, user.getType());

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				user.setName(rs.getString("name"));
				flag = true;// 表示该用户存在
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt);
		}
		return flag;
	}

	// 根据参数beginIndex，maxSize查询指定的页的记录到List集合
	@Override
	public List<User> findUserByPage(int beginIndex, int maxSize) {
		List<User> list = new ArrayList();// 保存查询的用户信息

		Connection conn = DbUtil.dbOpen(); // 实例对象初始化时调用DbUtil.dbOpen方法取得Connection对象
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			// 指定查询记录范围sql语句： select * from user limit beginIndex, maxSize;
			String sql = "SELECT * FROM user LIMIT ?,?";//
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, beginIndex);// 查询记录开始位置-1
			pstmt.setInt(2, maxSize);// 查询的每页显示的记录个数
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setUserName(rs.getString("user_name"));
				user.setType(rs.getInt("type"));
				user.setSex(rs.getInt("sex"));
				user.setBranch(rs.getInt("branch"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}
		return list;
	}

	// 实现统计user表的总的记录数
	@Override
	public int countUsers() {
		Connection conn = DbUtil.dbOpen(); // 实例对象初始化时调用DbUtil.dbOpen方法取得Connection对象
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int recordNums = 0;
		try {
			// 统计记录总数方法:select count(id) from user
			String sql = "SELECT count(id) FROM user ;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				recordNums = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}
		return recordNums;
	}

	/**
	 * 批量删除方法 ids：传入的删除的ID数组
	 */
	@Override
	public boolean deleteAllUser(String[] ids) {

		Connection con = DbUtil.dbOpen();
		// 定义一个int类型的数组，保存数据增加、删除、修改的每一批次返回的影响记录数
		int i = 0;
		PreparedStatement pst = null;

		// 方法1： 将所有选择的Id号拼凑为一个 where 字段 in ( ) SQL语句
		// delete from user where userid in (1,4,7,10);
		String sql = "delete from user where id in (";
		for (int j = 0; j < ids.length; j++) {
			if (j != ids.length - 1) {// 如果不是最后一个加逗号
				sql += ids[j] + ",";
			} else {//// 如果是最后一个加 )括号
				sql += ids[j] + ")";
			}
		}
		// 执行一条SQl处理语句
		try {
			pst = con.prepareStatement(sql);
			// 增加 删除 修改调用executeUpdate()方法，返回受影响的行数
			i = pst.executeUpdate();
		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst);
		}
		return i > 0 ? true : false;
	}

	public boolean deleteAllUserByBatch(String[] ids) {

		Connection con = DbUtil.dbOpen();
		// 定义一个int类型的数组，保存数据增加、删除、修改的每一批次返回的影响记录数
		int[] i = null;
		PreparedStatement pst = null;

		// String sql="delete from user where userid in( 1,2,3 ) ";

		String sql = "delete from user where id=?";
		try {
			// 调用Connection类的setAutoCommit(false)方法,将自动提交该为手动提交方式
			con.setAutoCommit(false);

			pst = con.prepareStatement(sql);
			for (int j = 0; j < ids.length; j++) {
				// 调用Integer包装类的parseInt(String),将数字字符串转换为整数
				pst.setInt(1, Integer.parseInt(ids[j]));
				// 调用PreparedStatement类的addBatch()方法，保存一次数据处理的有参数的sql语句
				pst.addBatch();
			}
			// 调用PreparedStatement类的executeBatch()方法，写入数据
			i = pst.executeBatch();
			// 正式向数据库写入数据
			con.commit();

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst);
		}
		return i.length > 0 ? true : false;
	}

	@Override
	public boolean findUserByName(String userName) {
		Connection conn = DbUtil.dbOpen(); // 实力对象初始化时调用DbUtil.dbOpen方法取得Connection对象
		boolean flag = false;// 定义一个boolean类型的变量保存用户是否存在的标记，
								// flag = false 表示 该 用户不存在

		PreparedStatement pstmt = null;

		try {
			String sql = "SELECT id FROM user WHERE user_name=?";

			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, userName);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;// 表示该用户存在
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt);
		}
		return flag;
	}

	@Override
	public boolean saveUser(User user) {

		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i = 0;

		try {
			pstmt = conn.prepareStatement(
					"update user set name=?, type=?, birthday=?, sex=?, branch=?, update_date=? where id=?");

			pstmt.setString(1, user.getName());
			pstmt.setInt(2, user.getType());
			pstmt.setString(3, sdf.format(user.getBirthday()));
			pstmt.setInt(4, user.getSex());
			pstmt.setInt(5, user.getBranch());
			pstmt.setString(6, sdf.format(user.getUpdateDate()));
			pstmt.setInt(7, user.getId());

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DbUtil.dbClose(conn, pstmt);

		}

		return i > 0;
	}

	@Override
	public boolean updatePassword(String userName, String newPassword) {

		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i = 0;

		try {
			pstmt = conn.prepareStatement("update user set password=? where user_name=?");

			pstmt.setString(1, newPassword);
			pstmt.setString(2, userName);

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DbUtil.dbClose(conn, pstmt);

		}

		return i > 0;
	}

	@Override
	public User findUser(String userName) {
		// 实例化一个User类的对象u用于保存查询的结果
		User user = new User();
		Connection con = DbUtil.dbOpen();
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			pst = con.prepareStatement("select * from user where user_name=?");
			pst.setString(1, userName);
			// 调用preparedStatment类的executeQuery()方法，返回查询的结果记录集合
			rs = pst.executeQuery();
			if (rs != null && rs.next()) {
				// 如果查询到当前id记录,将记录数据赋值给u对象
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setType(rs.getInt("type"));
				user.setBirthday(new Date(rs.getTimestamp("birthday").getTime()));
				user.setSex(rs.getInt("sex"));
				user.setBranch(rs.getInt("branch"));
				user.setAddDate(new Date(rs.getTimestamp("add_date").getTime()));
				user.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));
			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst, rs);
		}

		return user;
	}

	@Override
	public boolean saveMyData(User user) {

		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i = 0;

		try {
			pstmt = conn.prepareStatement("update user set name=?, birthday=?, sex=?, update_date=? where user_name=?");

			pstmt.setString(1, user.getName());
			pstmt.setString(2, sdf.format(user.getBirthday()));
			pstmt.setInt(3, user.getSex());
			pstmt.setString(4, sdf.format(user.getUpdateDate()));
			pstmt.setString(5, user.getUserName());

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DbUtil.dbClose(conn, pstmt);

		}

		return i > 0;
	}

}
